package com.goldcn.picture.service;

import com.aliyun.oss.OSSClient;
import com.goldcn.common.OSSManageUtil;
import com.goldcn.common.PropertyUtil;
import com.goldcn.common.util.IdGen;
import com.goldcn.common.util.UuidUtils;
import com.goldcn.config.OSSConfigure;
import com.goldcn.enumeration.OperateTypeEnum;
import com.goldcn.goods.model.GoodsSku;
import com.goldcn.goods.service.GoodsService;
import com.goldcn.goods.vo.GoodsDetailsVo;
import com.goldcn.goods.vo.GoodsInfoVo;
import com.goldcn.picture.model.PicAttrStone;
import com.goldcn.picture.model.PicSku;
import com.goldcn.picture.vo.PicImagesVo;
import com.goldcn.picture.vo.PicInfoDetailVo;
import com.goldcn.picture.vo.PicInfoVo;
import net.sf.json.JSONObject;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExcelPicImageService {

    private Logger logger = Logger.getLogger(ExcelPicImageService.class);

    @Autowired
    private OSSConfigure ossConfigure;

    @Autowired
    private GoodsService goodsService;

    @Autowired
    private PictureService pictureService;

    /**
     * 注意事项： 名称严格匹配，要跟云管控的后台一致
     * 蓝宝石属性
     * table_field_fieldType
     * fieldType: txt, list, integer,decimal
     * goods.txt为字段，list表示属性json
     */
    //private final static String[] skuKeys = {"goods_prdNo_txt", "goods_imgUrl_txt", "goods_hxStore_list_所在库", "sku_hxCutType_list_切工类型", "sku_hxColor_list_颜色", "goods_hxGradeNO_list_级别色号", "sku_hxShape_list_形状", "sku_hxSize_list_尺寸", "goods_hxHandleMethod_list_处理方法", "sku_hxNeatness_list_净度", "sku_hxLucency_list_透明度", "sku_hxCutGrade_list_切工级别", "sku_hxFireGrade_list_火彩级别", "sku_msWeight_decimal_重量", "sku_wholeSalePrice_decimal", "sku_price_decimal", "sku_inventory_integer", "goods_goodsPCDesc_txt"};

    //private final static String[] skuKeys = {"goods_prdNo_txt", "goods_imgUrl_txt", "goods_companyName_list_公司名称", "goods_klPrice_list_克拉单价", "goods_dlPrice_list_单粒价格", "goods_nativeGem_list_宝石种类", "goods_hxOrigin_list_产地", "goods_hxCutType_list_车工类型", "sku_hxColor_list_颜色级别", "sku_hxShape_list_形状", "sku_hxSize_list_尺寸规格", "goods_gemGrade_list_宝石级别", "goods_hxCutGrade_list_切工级别", "goods_hxLucency_list_透明度级别", "goods_hxFireGrade_list_火彩级别", "sku_hxNeatness_list_净度级别", "goods_hxHandleMethod_list_处理方法", "goods_hxKind_list_种类", "goods_hxSingleNum_list_粒数", "sku_weight_decimal_重量", "sku_wholeSalePrice_decimal", "sku_price_decimal", "sku_inventory_integer", "goods_memo_list_备注"};


    /**
     * 钻石属性
     */
    private final static String[] skuKeys_diamond = {"sku_hxShape_list_形状", "sku_msWeight_decimal_重量", "sku_msColor_list_颜色", "sku_msClarity_list_净度", "sku_msCut_list_切工", "sku_hxPolish_list_抛光", "sku_hxSym_list_对称性", "sku_hxLight_list_荧光性", "goods_GGcertOrg_list_证书鉴定机构", "sku_GGcertNum_txt_证书", "sku_wholeSalePrice_decimal", "sku_price_decimal", "sku_inventory_integer"};



    private final static List<String> keyList=new ArrayList<>();

    private final static Map<String, String> para = new HashMap<String, String>();

    private final static String FTYPE_TXT = "txt";
    private final static String FTYPE_LIST = "list";
    private final static String FTYPE_INTEGER = "integer";
    private final static String FTYPE_DECIMAL = "decimal";

    static {
        //生产
        para.put("hxorigin", "qNIkfC0JQ5ONbHsS4YYIXg");//hxsx1002
        para.put("hxbrand", "hxsx1002");
        para.put("hxNeatness", "KhBBpH0RFKGUsjK3rPQaw");
        para.put("hxHandleMethod", "GrlWtWWJS_eZNRHoDBs64w");
        para.put("hxFireGrade", "2_s9kakTWqQ05q56M074A");
        para.put("hxLucency", "pFX7fKRSGOCiAEt_jAy-Q");
        para.put("nativeGem", "JQUs_ZcaRPuvFEt8qdJ0yA");
        para.put("hxCutGrade", "gt-96HfTF6qTF-XIUhHtA");
        para.put("companyName", "7NRC39LBS8aYLmtvyh9MwA");
        para.put("klPrice", "vjHcOy8rSwSGUqxnejeZ6g");
        para.put("dlPrice", "wZVFrSkSQ-iY_N4_uBoEjQ");
        para.put("memo", "FcXw72b0TM6LF5lzKLU-9Q");
        para.put("hxSingleNum", "cIM3qyZ5QfauYrPV_MJkEA");
        para.put("hxKind", "bh0B2VB1Q7iJ3t3J1fokjQ");
        para.put("gemGrade", "zMjpeYf2QVG303fn08bWIQ");

        //
        //keyList.add("sku_hxStore_list_所在库");
        keyList.add("goods_barcodeno_txt_条码");
        keyList.add("goods_pictureurl_txt_图片");
        keyList.add("goods_mseries_txt_系列");
        keyList.add("attr_series_txt_大系列");
        keyList.add("attr_smailseries_txt_小系列");
        keyList.add("attr_suit_txt_套装");
        keyList.add("goods_picturename_txt_首饰名称");
        keyList.add("attr_bnumber_txt_款号");
        keyList.add("goods_hxorigin_list_产地");
        keyList.add("goods_hxbrand_list_品牌");
        keyList.add("goods_sslCode_txt_原编码");
        keyList.add("goods_pictureno_txt_内部款号");
        keyList.add("goods_dankan_list_是否单款单件");
        keyList.add("sku_hxHandSize_list_手寸");
        keyList.add("sku_Mosaic_list_材质");
        keyList.add("sku_gsWeight_txt_金属重量");
        keyList.add("sku_weight_txt_首饰总重");

        keyList.add("sku_msNumber_txt_主石编码");
        keyList.add("sku_msType_txt_主石类别");
        keyList.add("sku_msPrice_decimal_主石进价");
        keyList.add("sku_msPriceSum_decimal_主石金额");
        keyList.add("sku_hxShape_list_主石形状");
        keyList.add("sku_msPlace_list_主石产地");
        keyList.add("sku_msColor_list_主石颜色");
        keyList.add("sku_hxColor_list_主石色级");
        keyList.add("sku_hxNeatness_list_主石净度");
        keyList.add("sku_hxLucency_list_主石透明度");
        keyList.add("sku_hxFireGrade_list_主石火彩");
        keyList.add("sku_msHeight_list_主石高度");
        keyList.add("sku_hxCutType_list_主石切工类型");
        keyList.add("sku_hxCutGrade_list_主石切工级别");
        keyList.add("sku_size_list_主石尺寸规格");
        keyList.add("sku_hxSmNum_list_主石粒数");
        keyList.add("sku_msWeight_list_主石重量");
        keyList.add("sku_GGcertOrg_list_主石鉴定机构");
        keyList.add("sku_GGcertNum_txt_证书号");

        keyList.add("sku_ssNumber_txt_辅石1编号");
        keyList.add("sku_ssName_txt_辅石1名称");
        keyList.add("sku_ssSize_list_辅石1尺寸");
        keyList.add("sku_ssCount_list_辅石1粒数");
        keyList.add("sku_ssPrice_decimal_辅石1进价");
        keyList.add("sku_ssSum_decimal_辅石1金额");
        keyList.add("sku_sxShape_list_辅石1形状");
        keyList.add("sku_ssColor_list_辅石1颜色");
        keyList.add("sku_sxColorGrade_list_辅石1级别");
        keyList.add("sku_sxGradeNO_list_辅石1色号");
        keyList.add("sku_ssClarity_list_辅石1净度");
        keyList.add("sku_sxCutType_list_辅石1切工");
        keyList.add("sku_ssHeight_list_辅石1高度");
        keyList.add("sku_ssWeight_list_辅石1重量");
        keyList.add("sku_ssCertOrg_list_辅石1鉴定机构");
        keyList.add("sku_ssCertNum_txt_辅石1证书号");

        keyList.add("sku_psName_txt_配件1名");
        keyList.add("sku_psTexture_txt_配件1材质");
        keyList.add("sku_psCount_decimal_配件1数");
        keyList.add("sku_psWeight_decimal_配件1重");
        keyList.add("sku_psUnit_txt_配件1单位");
        keyList.add("sku_psPrice_decimal_配件1单价");
        keyList.add("sku_psSum_decimal_配件1金额");

        keyList.add("goods_gfStyle_list_工费款式");
        keyList.add("goods_gfLevel_list_工费级别");
        keyList.add("goods_gfType_list_进货工费方式");
        keyList.add("sku_craftPrice_decimal_工艺费");
        keyList.add("goods_jgType_list_加工费方式");
        keyList.add("goods_sellType_list_销售工费方式");
        keyList.add("sku_sellPrice_decimal_销售工费金额");

        keyList.add("sku_otherName_txt_其它费用名");
        keyList.add("sku_otherPrice_decimal_其它费用金额");
        keyList.add("sku_otherType_txt_其他费方式");
        keyList.add("goods_goldPrice_decimal_金价");
        keyList.add("sku_goldLose_txt_金耗");
        keyList.add("goods_ssUnit_txt_单位");
        keyList.add("sku_ssCost_txt_进货成本");
        keyList.add("sku_sellBase_txt_售价成本基数");
        keyList.add("sku_sellCost_txt_售价成本");
        keyList.add("goods_sellRate_list_销售倍率");
        keyList.add("sku_slPrice_decimal_售价");
        keyList.add("sku_msRatio_txt_金工石占比");
        keyList.add("sku_msSection_txt_价格区间");
        keyList.add("goods_picturedescribe_txt_备注");

    }


    private final static int BEGIN_ROW = 1;
    private final static int BEGIN_COLUMN = 1;

    private final static int BEGIN_ROW_HX = 1;
    private final static int BEGIN_COLUMN_HX = 0;


    /**
     * 导入蓝帝裸石
     *
     * @param fileName
     * @param picInfoVo
     * @return
     * @throws Exception
     */
    public Object importXlsLD(String fileName, PicInfoVo picInfoVo) throws Exception {

        Long begin = System.currentTimeMillis();

        Map message = new HashMap();
        //String ftpPath = ossConfigure.getFtpDir();
        //InputStream inputStream = new FileInputStream(ftpPath + File.separator + fileName);
        InputStream inputStream = new FileInputStream("./file/"+fileName);

        HSSFWorkbook workbook = (HSSFWorkbook) WorkbookFactory.create(inputStream);
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);
        Map<String, Object> map = new HashMap<String, Object>();

        //图片集合
        List<PicImagesVo> picImagesVos=new ArrayList<>();


        //图片预处理
        List<HSSFPictureData> list = workbook.getAllPictures();
        if (list!=null && list.size()>0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    map.put(pictureIndex + "", picData);
                }
            }
        }

        OSSClient ossClient = null;
        String imageName = "";

        Map<String, String> xlsImgList = new HashMap<String, String>();
/*        try {
            ossClient = OSSManageUtil.getOSSClient(ossConfigure);
            for (Map.Entry me : map.entrySet()) {
                HSSFPictureData picData = (HSSFPictureData) me.getValue();
                imageName = UuidUtils.uuid() + ".png";
                imageName = OSSManageUtil.uploadFile(ossClient, ossConfigure.getBucketName(), picData.getData(), imageName);
                //savePic(me.getKey() + "", picData);
                //PicImagesVo picImagesVo=new PicImagesVo();
                //picImagesVo.setUrl(imageName);
                //picImagesVos.add(picImagesVo);
                xlsImgList.put(me.getKey() + "", imageName);
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("xls图片上传失败", e);
        } finally {
            OSSManageUtil.closeOSSClient(ossClient);
        }
        logger.info("导入图片ok！数量====" + map.size());*/


        GoodsDetailsVo goodsDetailsVo = new GoodsDetailsVo();
        PicInfoDetailVo picInfoDetailVo=new PicInfoDetailVo();
        picInfoDetailVo.setOperateType(OperateTypeEnum.insert);
        List<String> imageUrlList = new ArrayList<String>();
        List<GoodsSku> goodsSkuList = new ArrayList<>();
        List<JSONObject> goodsAttrList = new ArrayList<>();

        List<PicSku> picSkuList=new ArrayList<>();


        StringBuffer skuName = new StringBuffer();
        StringBuffer goodsAttr = new StringBuffer();


        String key = "";
        Object cellValue = null;
        String[] temp, stemp = null;
        String field, fieldType, fieldName, tableName = null;

        //遍历数据
        int i = 0;
        List<String> listTable=new ArrayList<>();
        for (Row row : sheet) {
            if (row.getRowNum() < BEGIN_ROW) {
                for (Cell cell:row){
                    listTable.add(StringUtils.trim(cell.getRichStringCellValue().getString()));
                    System.out.println(StringUtils.trim(cell.getRichStringCellValue().getString()));
                }
                continue;
            }
            //skuJSON
            JSONObject jsonObject = new JSONObject();
            //attrJSON
            JSONObject jsonAttr=new JSONObject();

            GoodsSku goodsSku = new GoodsSku();
            PicSku picSku=new PicSku();
            //goodsJson
            JSONObject jsonObjectGoods = new JSONObject();

            for (Cell cell : row) {
                if (cell.getColumnIndex() < BEGIN_COLUMN) {
                    continue;
                }
                key = null;
                cellValue = null;
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        //key = skuKeys[cell.getColumnIndex() - 1];
                        key=keyList.get(cell.getColumnIndex()-1);
                        cellValue = StringUtils.trim(cell.getRichStringCellValue().getString());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        //key = skuKeys[cell.getColumnIndex() - 1];
                        key=keyList.get(cell.getColumnIndex()-1);
                        cellValue = cell.getNumericCellValue();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        break;
                    default:
                }
                if (key != null) {
                    temp = key.split("_");
                    field = temp[1];
                    fieldType = temp[2];
                    tableName = temp[0];
                    if (tableName.equalsIgnoreCase("goods")) {
                        //产品信息
                        if (fieldType.equalsIgnoreCase(FTYPE_TXT)) {
                            //System.out.println(field+"...field");
                            PropertyUtil.setValue(picInfoVo, field, cellValue);
                        } else {
                            if (cellValue instanceof String) {

                                fieldName = temp[3];
                                stemp = ((String) cellValue).split(" ");
                                if (stemp.length > 1 && !field.equalsIgnoreCase("memo")) {
                                    cellValue = stemp[stemp.length - 1];
                                }
                                //找回属性id
/*                                if (para.get(field) != null) {
                                    jsonObjectGoods.put("cfadid", para.get(field));
                                } else {
                                    jsonObjectGoods.put("cfadid", stemp[0]);
                                }
                                jsonObjectGoods.put("clazzattrcode", field);
                                jsonObjectGoods.put("clazzattrval", cellValue);
                                jsonObjectGoods.put("clazzattrname", fieldName);*/
                                jsonObjectGoods.put(field,cellValue);
                                //goodsAttr.append()
                            } else if (cellValue instanceof Number) {

                               // JSONObject jsonObjectGoods = new JSONObject();
                                fieldName = temp[3];
                                //找回属性id
/*                                if (para.get(field) != null) {
                                    jsonObjectGoods.put("cfadid", para.get(field));
                                } else {
                                    jsonObjectGoods.put("cfadid", stemp[0]);
                                }
                                jsonObjectGoods.put("clazzattrcode", field);
                                jsonObjectGoods.put("clazzattrval", cellValue);
                                jsonObjectGoods.put("clazzattrname", fieldName);*/
                                jsonObjectGoods.put(field,cellValue);
                                //goodsAttrList.add(jsonObjectGoods);


                            }
                        }
                    } else if (tableName.equalsIgnoreCase("sku")) {
                        //产品sku信息
                        if (cellValue instanceof String) {
                            fieldName = temp[3];
                            stemp = ((String) cellValue).split(" ");
                            if (stemp.length > 1) {
                                cellValue = stemp[stemp.length - 1];
                            }
                            PropertyUtil.setValue(picSku, field, cellValue);
                            jsonObject.put(field, cellValue);
                            skuName.append(fieldName + ":" + cellValue + "+");
                        } else if (cellValue instanceof Number) {

                            if (fieldType.equalsIgnoreCase(FTYPE_INTEGER)) {
                                PropertyUtil.setValue(picSku, field, new BigDecimal(cellValue.toString()).intValue());
                            } else if (fieldType.equalsIgnoreCase(FTYPE_DECIMAL)) {
                                PropertyUtil.setValue(picSku, field, new BigDecimal(cellValue.toString()));
                            } else if (fieldType.equalsIgnoreCase(FTYPE_LIST)) {
                                fieldName = temp[3];
                                jsonObject.put(field, cellValue);
                                skuName.append(fieldName + ":" + cellValue + "+");
                            }

                            //重量单独处理
                            if (field.equalsIgnoreCase("weight")) {
                                fieldName = temp[3];
                                jsonObject.put(field, cellValue);
                                skuName.append(fieldName + ":" + cellValue + "+");
                            }
                        }
                    }else if (tableName.equalsIgnoreCase("attr")) {
                        //产品sku信息
                        if (cellValue instanceof String) {
                            fieldName = temp[3];
                            stemp = ((String) cellValue).split(" ");
                            if (stemp.length > 1) {
                                cellValue = stemp[stemp.length - 1];
                            }
                            jsonAttr.put(field, cellValue);
                        } else if (cellValue instanceof Number) {

                            if (fieldType.equalsIgnoreCase(FTYPE_INTEGER)) {
                                PropertyUtil.setValue(picSku, field, new BigDecimal(cellValue.toString()).intValue());
                            } else if (fieldType.equalsIgnoreCase(FTYPE_DECIMAL)) {
                                PropertyUtil.setValue(picSku, field, new BigDecimal(cellValue.toString()));
                            } else if (fieldType.equalsIgnoreCase(FTYPE_LIST)) {
                                fieldName = temp[3];
                                jsonObject.put(field, cellValue);
                                skuName.append(fieldName + ":" + cellValue + "+");
                            }

                            //重量单独处理
                            if (field.equalsIgnoreCase("weight")) {
                                fieldName = temp[3];
                                jsonObject.put(field, cellValue);
                                skuName.append(fieldName + ":" + cellValue + "+");
                            }
                        }
                    }
                }
            }
            //goodsAttrList.add(jsonObjectGoods);
/*            if (goodsAttrList.size() > 0) {
                picInfoVo.setAttr(goodsAttrList.toString());
            } else {
                picInfoVo.setAttr("[]");
            }*/
            picInfoVo.setAttr(jsonObjectGoods.toString());
            //goodsInfoVo.setGoodsAPPDesc("<p>" + goodsInfoVo.getGoodsPCDesc() + "<br/></p>");
            //goodsInfoVo.setGoodsPCDesc("<html><head lang=\\\"en\\\"><meta name=\\\"viewport\\\" content=\\\"width=device-width,initial-scale=1,maximum-scale=1,user-scalable=0\\\"><meta content=\\\"telephone=no\\\" name=\\\"format-detection\\\"><meta name=\\\"apple-mobile-web-app-capable\\\" content=\\\"yes\\\"><meta content=\\\"\\\" name=\\\"keywords\\\"><meta content=\\\"\\\" name=\\\"description\\\"><meta name=\\\"viewport\\\" content=\\\"width=device-width,initial-scale=1,user-scalable=0,minimum-scale=1,maximum-scale=1\\\"><meta name=\\\"apple-mobile-web-app-capable\\\" content=\\\"yes\\\"><meta name=\\\"apple-mobile-web-app-status-bar-style\\\" content=\\\"black\\\"><style type=\\\"text/css\\\">* {margin: 0;padding: 0}body {position: relative;font: 14px \\\"microsoft yahei\\\";}</style></head><body><p>" + goodsInfoVo.getGoodsPCDesc() + "<br/></p></body></html>");

            if (skuName.toString().length() > 1) {
                jsonObject.put("name", skuName.toString().substring(0, skuName.toString().length() - 1));
            } else {
                jsonObject.put("name", skuName.toString());
            }
/*            goodsSku.setAttrJson(jsonObject.toString());
            goodsSkuList.add(goodsSku);
            goodsDetailsVo.setGoodsSkuList(goodsSkuList);*/

            picInfoVo.setPictureid(IdGen.get().nextId());
            picSku.setPictureid(picInfoVo.getPictureid());
            picSku.setArrtjson(jsonObject.toString());
            picSkuList.add(picSku);
            picInfoDetailVo.setPicInfo(picInfoVo);
            picInfoDetailVo.setPicSkuList(picSkuList);
            PicAttrStone picAttrStone=new PicAttrStone();
            picAttrStone.setPictureid(picInfoVo.getPictureid());
            picAttrStone.setAge("20");
            picInfoDetailVo.setPicAttrStone(picAttrStone);

            //if (i < xlsImgList.size()) {
               // PicImagesVo picImagesVo=new PicImagesVo();
                //picImagesVo.setUrl(xlsImgList.get(i + ""));
                //picImagesVos.add(picImagesVo);
                //imageUrlList.add(xlsImgList.get(i + ""));
                //goodsDetailsVo.setImageUrlList(imageUrlList);
                //picInfoDetailVo.setPicImgs(picImagesVos);
                try {
                    //goodsService.operateGoods(goodsDetailsVo);

                    pictureService.addPicInfo(picInfoDetailVo);
                } catch (Exception ex) {
                    ex.printStackTrace();
                }
            //}
            //清除数据
            imageUrlList.clear();
            skuName.setLength(0);
            //goodsSkuList.clear();
            //goodsAttrList.clear();
            picSkuList.clear();

            i++;
        }
        try {
            inputStream.close();
            //File folderFile = new File(ftpPath + File.separator + fileName);
            File folderFile = new File("./file/" + fileName);
            if (folderFile.isFile()) {
                folderFile.delete();
                folderFile = null;
            }
        } catch (Exception ex) {
            logger.error(ex);
        } finally {
            inputStream = null;
        }
        message.put("success", "从xls成功导入(" + i + ")条记录,总共耗时" + (System.currentTimeMillis() - begin) + "毫秒");
        return message;

    }





    /**
     * 导入蓝帝裸石
     *
     * @param fileName
     * @param picInfoVo
     * @return
     * @throws Exception
     */
    public Object importXlsLDS(String fileName, PicInfoVo picInfoVo) throws Exception {

        Long begin = System.currentTimeMillis();

        Map message = new HashMap();
        //String ftpPath = ossConfigure.getFtpDir();
        //InputStream inputStream = new FileInputStream(ftpPath + File.separator + fileName);
        InputStream inputStream = new FileInputStream("./file/"+fileName);

        HSSFWorkbook workbook = (HSSFWorkbook) WorkbookFactory.create(inputStream);
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);
        Map<String, Object> map = new HashMap<String, Object>();

        //图片集合
        List<PicImagesVo> picImagesVos=new ArrayList<>();


        //图片预处理
        List<HSSFPictureData> list = workbook.getAllPictures();
        if (list!=null && list.size()>0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    map.put(pictureIndex + "", picData);
                }
            }
        }

        OSSClient ossClient = null;
        String imageName = "";

        Map<String, String> xlsImgList = new HashMap<String, String>();
/*        try {
            ossClient = OSSManageUtil.getOSSClient(ossConfigure);
            for (Map.Entry me : map.entrySet()) {
                HSSFPictureData picData = (HSSFPictureData) me.getValue();
                imageName = UuidUtils.uuid() + ".png";
                imageName = OSSManageUtil.uploadFile(ossClient, ossConfigure.getBucketName(), picData.getData(), imageName);
                //savePic(me.getKey() + "", picData);
                //PicImagesVo picImagesVo=new PicImagesVo();
                //picImagesVo.setUrl(imageName);
                //picImagesVos.add(picImagesVo);
                xlsImgList.put(me.getKey() + "", imageName);
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error("xls图片上传失败", e);
        } finally {
            OSSManageUtil.closeOSSClient(ossClient);
        }
        logger.info("导入图片ok！数量====" + map.size());*/

        GoodsDetailsVo goodsDetailsVo = new GoodsDetailsVo();
        PicInfoDetailVo picInfoDetailVo=new PicInfoDetailVo();
        picInfoDetailVo.setOperateType(OperateTypeEnum.insert);
        List<String> imageUrlList = new ArrayList<String>();
        List<GoodsSku> goodsSkuList = new ArrayList<>();
        List<JSONObject> goodsAttrList = new ArrayList<>();

        List<PicSku> picSkuList=new ArrayList<>();

        StringBuffer skuName = new StringBuffer();
        StringBuffer goodsAttr = new StringBuffer();
        PicAttrStone picAttrStone=null;

        String key = "";
        Object cellValue = null;
        String[] temp, stemp = null;
        String field, fieldType, fieldName, tableName,tName = null;

        //遍历数据
        int i = 0;
        List<String> listTable=new ArrayList<>();
        for (Row row : sheet) {
            if (row.getRowNum() < BEGIN_ROW) {
                for (Cell cell:row){
                    listTable.add(StringUtils.trim(cell.getRichStringCellValue().getString()));
                    //System.out.println(StringUtils.trim(cell.getRichStringCellValue().getString()));
                }
                continue;
            }
            //skuJSON 商品规格
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("repertory",1);//数量
            jsonObject.put("wholesaleprice","");//成本价
            jsonObject.put("retailprice","");//销售价

            //商品属性
            JSONObject jsonAttr=new JSONObject();
            GoodsSku goodsSku = new GoodsSku();
            PicSku picSku=new PicSku();
            picAttrStone=new PicAttrStone();

            for (int s=1;s<listTable.size();s++){
                String keyanme=listTable.get(s);
                for (String keys:keyList){
                    key=keys;
                    cellValue=null;
                    if (key!=null){
                        temp = key.split("_");
                        field = temp[1];
                        fieldType = temp[2];
                        tableName = temp[0];
                        tName=temp[3];//表头名称
                        if (keyanme.equals(tName)){
                            //System.out.println(keyanme+"......"+tName+"....."+field);
                            switch (row.getCell(s).getCellType()) {
                                case Cell.CELL_TYPE_STRING:
                                    cellValue = StringUtils.trim(row.getCell(s).getRichStringCellValue().getString());
                                    break;
                                case Cell.CELL_TYPE_NUMERIC:
                                    cellValue = row.getCell(s).getNumericCellValue();
                                    break;
                                case Cell.CELL_TYPE_BOOLEAN:
                                    break;
                                default:
                            }
                            if (tableName.equalsIgnoreCase("goods")) {
                                //产品信息
                                if (fieldType.equalsIgnoreCase(FTYPE_TXT)) {
                                    //System.out.println(field+"...field");
                                    PropertyUtil.setValue(picInfoVo, field, cellValue);
                                } else {
                                    if (cellValue instanceof String) {
                                        //goodsJson
                                        JSONObject jsonObjectGoods = new JSONObject();
                                        fieldName = temp[3];
                                        stemp = ((String) cellValue).split(" ");
                                        if (stemp.length > 1 && !field.equalsIgnoreCase("memo")) {
                                            cellValue = stemp[stemp.length - 1];
                                        }
                                        //找回属性id
                                        if (para.get(field) != null) {
                                            jsonObjectGoods.put("cfadid", para.get(field));
                                        } else {
                                            jsonObjectGoods.put("cfadid", stemp[0]);
                                        }
                                        jsonObjectGoods.put("clazzattrcode", field);
                                        jsonObjectGoods.put("clazzattrval", cellValue);
                                        jsonObjectGoods.put("clazzattrname", fieldName);
                                        goodsAttrList.add(jsonObjectGoods);
                                        //jsonObjectGoods.put(field,cellValue);
                                        //goodsAttr.append()
                                    } else if (cellValue instanceof Number) {
                                        JSONObject jsonObjectGoods = new JSONObject();
                                        fieldName = temp[3];
                                        //找回属性id
                                        if (para.get(field) != null) {
                                            jsonObjectGoods.put("cfadid", para.get(field));
                                        } else {
                                            jsonObjectGoods.put("cfadid", stemp[0]);
                                        }
                                        jsonObjectGoods.put("clazzattrcode", field);
                                        jsonObjectGoods.put("clazzattrval", cellValue);
                                        jsonObjectGoods.put("clazzattrname", fieldName);
                                        //jsonObjectGoods.put(field,cellValue);
                                        goodsAttrList.add(jsonObjectGoods);
                                    }
                                }
                            }else if (tableName.equalsIgnoreCase("sku")) {
                                //System.out.println(cellValue+"....sku.....cellValue");
                                if (cellValue==null || cellValue.equals("")){
                                    jsonObject.put(field, "");
                                }
                                //产品sku信息
                                if (cellValue instanceof String) {
                                    fieldName = temp[3];
                                    stemp = ((String) cellValue).split(" ");
                                    if (stemp.length > 1) {
                                        //cellValue = stemp[stemp.length - 1];
                                    }
                                    //PropertyUtil.setValue(picSku, field, cellValue);
                                    jsonObject.put(field, cellValue);
                                    skuName.append(fieldName + ":" + cellValue + "+");
                                } else if (cellValue instanceof Number) {
                                    if (fieldType.equalsIgnoreCase(FTYPE_INTEGER)) {
                                        jsonObject.put(field, cellValue);
                                        PropertyUtil.setValue(picSku, field, new BigDecimal(cellValue.toString()).intValue());
                                    } else if (fieldType.equalsIgnoreCase(FTYPE_DECIMAL)) {
                                        jsonObject.put(field, cellValue);
                                        PropertyUtil.setValue(picSku, field, new BigDecimal(cellValue.toString()));
                                    } else if (fieldType.equalsIgnoreCase(FTYPE_LIST)) {
                                        fieldName = temp[3];
                                        jsonObject.put(field, cellValue);
                                        skuName.append(fieldName + ":" + cellValue + "+");
                                    }else{
                                        fieldName = temp[3];
                                        jsonObject.put(field, cellValue);
                                        skuName.append(fieldName + ":" + cellValue + "+");
                                    }

                                    //重量单独处理
                                    if (field.equalsIgnoreCase("weight")) {
                                        fieldName = temp[3];
                                        jsonObject.put(field, cellValue);
                                        skuName.append(fieldName + ":" + cellValue + "+");
                                    }
                                    if (field.equalsIgnoreCase("gsWeight")) {
                                        fieldName = temp[3];
                                        jsonObject.put(field, cellValue);
                                        skuName.append(fieldName + ":" + cellValue + "+");
                                    }
                                }
                            }else if (tableName.equalsIgnoreCase("attr")) {
                                //产品sku信息
                                if (cellValue instanceof String) {
                                    PropertyUtil.setValue(picAttrStone, field, cellValue);
                                }
                            }
                            break;
                        }
                    }
                }
            }

            if (goodsAttrList.size() > 0) {
                picInfoVo.setAttr(goodsAttrList.toString());
            } else {
                picInfoVo.setAttr("[]");
            }
            ///picInfoVo.setAttr(jsonObjectGoods.toString());
            //goodsInfoVo.setGoodsAPPDesc("<p>" + goodsInfoVo.getGoodsPCDesc() + "<br/></p>");
            //goodsInfoVo.setGoodsPCDesc("<html><head lang=\\\"en\\\"><meta name=\\\"viewport\\\" content=\\\"width=device-width,initial-scale=1,maximum-scale=1,user-scalable=0\\\"><meta content=\\\"telephone=no\\\" name=\\\"format-detection\\\"><meta name=\\\"apple-mobile-web-app-capable\\\" content=\\\"yes\\\"><meta content=\\\"\\\" name=\\\"keywords\\\"><meta content=\\\"\\\" name=\\\"description\\\"><meta name=\\\"viewport\\\" content=\\\"width=device-width,initial-scale=1,user-scalable=0,minimum-scale=1,maximum-scale=1\\\"><meta name=\\\"apple-mobile-web-app-capable\\\" content=\\\"yes\\\"><meta name=\\\"apple-mobile-web-app-status-bar-style\\\" content=\\\"black\\\"><style type=\\\"text/css\\\">* {margin: 0;padding: 0}body {position: relative;font: 14px \\\"microsoft yahei\\\";}</style></head><body><p>" + goodsInfoVo.getGoodsPCDesc() + "<br/></p></body></html>");

            if (skuName.toString().length() > 1) {
                jsonObject.put("name", skuName.toString().substring(0, skuName.toString().length() - 1));
            } else {
                jsonObject.put("name", skuName.toString());
            }
/*            goodsSku.setAttrJson(jsonObject.toString());
            goodsSkuList.add(goodsSku);
            goodsDetailsVo.setGoodsSkuList(goodsSkuList);*/

            picInfoVo.setPictureid(IdGen.get().nextId());
            //picInfoVo.setAttr(jsonAttr.toString());
            picSku.setPictureid(picInfoVo.getPictureid());
            picSku.setArrtjson(jsonObject.toString());
            picSkuList.add(picSku);
            picInfoDetailVo.setPicInfo(picInfoVo);
            picInfoDetailVo.setPicSkuList(picSkuList);
            //PicAttrStone picAttrStone=new PicAttrStone();
            picAttrStone.setPictureid(picInfoVo.getPictureid());
            picAttrStone.setAge("20");
            if (picAttrStone.getSeries()!=null){
                picAttrStone.setSeriesid(4352342342L);
            }
            if (picAttrStone.getSmailseries()!=null){
                picAttrStone.setSmailseriesid(4345675676L);
            }
            picInfoDetailVo.setPicAttrStone(picAttrStone);

            //if (i < xlsImgList.size()) {
            // PicImagesVo picImagesVo=new PicImagesVo();
            //picImagesVo.setUrl(xlsImgList.get(i + ""));
            //picImagesVos.add(picImagesVo);
            //imageUrlList.add(xlsImgList.get(i + ""));
            //goodsDetailsVo.setImageUrlList(imageUrlList);
            //picInfoDetailVo.setPicImgs(picImagesVos);
            try {
                //goodsService.operateGoods(goodsDetailsVo);

                pictureService.addPicInfo(picInfoDetailVo);
            } catch (Exception ex) {
                ex.printStackTrace();
            }
            //}
            //清除数据
            imageUrlList.clear();
            skuName.setLength(0);
            //goodsSkuList.clear();
            goodsAttrList.clear();
            picSkuList.clear();
            i++;
        }
        try {
            inputStream.close();
            //File folderFile = new File(ftpPath + File.separator + fileName);
            File folderFile = new File("./file/" + fileName);
            if (folderFile.isFile()) {
                folderFile.delete();
                folderFile = null;
            }
        } catch (Exception ex) {
            logger.error(ex);
        } finally {
            inputStream = null;
        }
        message.put("success", "从xls成功导入(" + i + ")条记录,总共耗时" + (System.currentTimeMillis() - begin) + "毫秒");
        return message;

    }


    /**
     * 导入恒信钻石
     *
     * @param fileName
     * @param goodsInfoVo
     * @return
     * @throws Exception
     */
    public Object importXlsHX(String fileName, GoodsInfoVo goodsInfoVo) throws Exception {

        Long begin = System.currentTimeMillis();

        Map message = new HashMap();
        String ftpPath = ossConfigure.getFtpDir();
        InputStream inputStream = new FileInputStream(ftpPath + File.separator + fileName);
        //InputStream inputStream = new FileInputStream("f:/3.xls");

        HSSFWorkbook workbook = (HSSFWorkbook) WorkbookFactory.create(inputStream);
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);

        Map<String, Object> map = new HashMap<String, Object>();

        //图片预处理
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;
                int pictureIndex = pic.getPictureIndex() - 1;
                HSSFPictureData picData = pictures.get(pictureIndex);
                map.put(pictureIndex + "", picData);
            }
        }

        OSSClient ossClient = null;
        String imageName = "";

        Map<String, String> xlsImgList = new HashMap<String, String>();
        try {
            ossClient = OSSManageUtil.getOSSClient(ossConfigure);
            for (Map.Entry me : map.entrySet()) {
                HSSFPictureData picData = (HSSFPictureData) me.getValue();
                imageName = UuidUtils.uuid() + ".png";
                imageName = OSSManageUtil.uploadFile(ossClient, ossConfigure.getBucketName(), picData.getData(), imageName);
                //savePic(me.getKey() + "", picData);
                xlsImgList.put(me.getKey() + "", imageName);
            }
        } catch (Exception e) {
            logger.info(e.getMessage());
        } finally {
            OSSManageUtil.closeOSSClient(ossClient);
        }

        logger.info("导入图片ok！数量====" + map.size());

        GoodsDetailsVo goodsDetailsVo = new GoodsDetailsVo();
        goodsDetailsVo.setOperateType(OperateTypeEnum.insert);

        List<GoodsSku> goodsSkuList = new ArrayList<>();
        List<String> imageUrlList = new ArrayList<>();
        List<JSONObject> goodsAttrList = new ArrayList<>();

        StringBuffer skuName = new StringBuffer();

        String key = "";
        Object cellValue = null;
        String[] temp, stemp = null;
        String field, fieldType, fieldName, tableName = null;

        //遍历数据
        int i = 0;
        for (Row row : sheet) {
            if (row.getRowNum() < BEGIN_ROW_HX) {
                continue;
            }
            //skuJSON
            JSONObject jsonObject = new JSONObject();
            GoodsSku goodsSku = new GoodsSku();

            for (Cell cell : row) {
                if (cell.getColumnIndex() < BEGIN_COLUMN_HX) {
                    continue;
                }
                key = null;
                cellValue = null;
                /*证书号  读取数字单元格内容转为文本类型 */
                if (cell.getColumnIndex() == 10) {
                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                }
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        key = skuKeys_diamond[cell.getColumnIndex() - 1];
                        cellValue = StringUtils.trim(cell.getRichStringCellValue().getString());
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        key = skuKeys_diamond[cell.getColumnIndex() - 1];
                        cellValue = cell.getNumericCellValue();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        break;
                    default:
                }
                if (key != null) {
                    temp = key.split("_");
                    field = temp[1];
                    fieldType = temp[2];
                    tableName = temp[0];
                    if (tableName.equalsIgnoreCase("goods")) {

                        //产品信息
                        if (fieldType.equalsIgnoreCase(FTYPE_TXT)) {
                            PropertyUtil.setValue(goodsInfoVo, field, cellValue);
                        } else {
                            if (cellValue instanceof String) {
                                //goodsJson
                                JSONObject jsonObjectGoods = new JSONObject();

                                fieldName = temp[3];
                                stemp = ((String) cellValue).split(" ");
                                if (stemp.length > 1) {
                                    cellValue = stemp[stemp.length - 1];
                                }
                                //找回属性id
                                if (para.get(field) != null) {
                                    jsonObjectGoods.put("cfadid", para.get(field));
                                } else {
                                    jsonObjectGoods.put("cfadid", stemp[0]);
                                }
                                jsonObjectGoods.put("clazzattrcode", field);
                                jsonObjectGoods.put("clazzattrval", cellValue);
                                jsonObjectGoods.put("clazzattrname", fieldName);
                                goodsAttrList.add(jsonObjectGoods);
                            }
                        }
                    } else if (tableName.equalsIgnoreCase("sku")) {
                        //产品sku信息
                        if (cellValue instanceof String) {
                            fieldName = temp[3];
                            stemp = ((String) cellValue).split(" ");
                            if (stemp.length > 1) {
                                cellValue = stemp[stemp.length - 1];
                            }
                            PropertyUtil.setValue(goodsSku, field, cellValue);
                            jsonObject.put(field, cellValue);
                            skuName.append(fieldName + ":" + cellValue + "+");
                            //证书号作为款号
                            if (field.equalsIgnoreCase("GGcertNum")) {
                                goodsInfoVo.setPrdNo(cellValue.toString());
                                goodsInfoVo.setGoodsPCDesc(cellValue.toString());
                            }
                        } else if (cellValue instanceof Number) {
                            if (fieldType.equalsIgnoreCase(FTYPE_INTEGER)) {
                                PropertyUtil.setValue(goodsSku, field, new BigDecimal(cellValue.toString()).intValue());
                            } else if (fieldType.equalsIgnoreCase(FTYPE_DECIMAL)) {
                                PropertyUtil.setValue(goodsSku, field, new BigDecimal(cellValue.toString()));
                            } else if (fieldType.equalsIgnoreCase(FTYPE_LIST)) {
                                fieldName = temp[3];
                                jsonObject.put(field, cellValue);
                                skuName.append(fieldName + ":" + cellValue + "+");
                            }
                            //重量单独处理
                            if (field.equalsIgnoreCase("msWeight")) {
                                fieldName = temp[3];
                                jsonObject.put(field, cellValue);
                                skuName.append(fieldName + ":" + cellValue + "+");
                            }
                        }
                    }
                }
            }

            if (goodsAttrList.size() > 0) {
                goodsInfoVo.setAttr(goodsAttrList.toString());
            } else {
                goodsInfoVo.setAttr("[]");
            }

            goodsInfoVo.setGoodsAPPDesc("<p>" + goodsInfoVo.getGoodsPCDesc() + "<br/></p>");
            goodsInfoVo.setGoodsPCDesc("<html><head lang=\\\"en\\\"><meta name=\\\"viewport\\\" content=\\\"width=device-width,initial-scale=1,maximum-scale=1,user-scalable=0\\\"><meta content=\\\"telephone=no\\\" name=\\\"format-detection\\\"><meta name=\\\"apple-mobile-web-app-capable\\\" content=\\\"yes\\\"><meta content=\\\"\\\" name=\\\"keywords\\\"><meta content=\\\"\\\" name=\\\"description\\\"><meta name=\\\"viewport\\\" content=\\\"width=device-width,initial-scale=1,user-scalable=0,minimum-scale=1,maximum-scale=1\\\"><meta name=\\\"apple-mobile-web-app-capable\\\" content=\\\"yes\\\"><meta name=\\\"apple-mobile-web-app-status-bar-style\\\" content=\\\"black\\\"><style type=\\\"text/css\\\">* {margin: 0;padding: 0}body {position: relative;font: 14px \\\"microsoft yahei\\\";}</style></head><body><p>" + goodsInfoVo.getGoodsPCDesc() + "<br/></p></body></html>");

            if (skuName.toString().length() > 1) {
                jsonObject.put("name", skuName.toString().substring(0, skuName.toString().length() - 1));
            } else {
                jsonObject.put("name", skuName.toString());
            }
            goodsSku.setAttrJson(jsonObject.toString());
            goodsSkuList.add(goodsSku);
            goodsDetailsVo.setGoodsSkuList(goodsSkuList);

            goodsDetailsVo.setGoodsInfoVo(goodsInfoVo);


            if (xlsImgList.get(i + "") != null) {
                imageUrlList.add(xlsImgList.get(i + ""));
            } else {
                imageUrlList.add(xlsImgList.get("0"));
            }
            goodsDetailsVo.setImageUrlList(imageUrlList);

            //保存数据
            goodsService.operateGoods(goodsDetailsVo);

            //清除数据
            imageUrlList.clear();
            skuName.setLength(0);
            goodsSkuList.clear();
            goodsAttrList.clear();

            i++;
        }
        try {
            inputStream.close();
            File folderFile = new File(ftpPath + File.separator + fileName);
            if (folderFile.isFile()) {
                folderFile.delete();
                folderFile = null;
            }
        } catch (Exception ex) {
            logger.error(ex);
        } finally {
            inputStream = null;
        }
        message.put("success", "从xls成功导入(" + i + ")条记录,总共耗时" + (System.currentTimeMillis() - begin) + "毫秒");
        return message;

    }

    private static void savePic(String i, PictureData pic) throws Exception {
        String ext = pic.suggestFileExtension();
        byte[] data = pic.getData();
        if (ext.equalsIgnoreCase("png") || ext.equalsIgnoreCase("jpg") || ext.equalsIgnoreCase("jpeg")) {
            FileOutputStream out = new FileOutputStream("F:\\" + i + ".jpg");
            out.write(data);
            out.close();
        }

    }


    public static void main1(String[] ds) throws Exception {

        ExcelPicImageService xx = new ExcelPicImageService();
        xx.importXlsHX(null, new GoodsInfoVo());

    }

    public static void main2(String[] ds) throws Exception {

        InputStream inputStream = new FileInputStream("f://1.xls");
        HSSFWorkbook workbook = (HSSFWorkbook) WorkbookFactory.create(inputStream);
        List<HSSFPictureData> pictures = workbook.getAllPictures();
        HSSFSheet sheet = (HSSFSheet) workbook.getSheetAt(0);
        Map<String, Object> map = new HashMap<String, Object>();


        //图片预处理
        int i = 1;
        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
            HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
            if (shape instanceof HSSFPicture) {
                HSSFPicture pic = (HSSFPicture) shape;
                int row = anchor.getRow2();
                int col = anchor.getCol2();
                int pictureIndex = pic.getPictureIndex() - 1;
                HSSFPictureData picData = pictures.get(pictureIndex);

                //map.put(row + ":" + col, picData);
                savePic(pictureIndex + "", picData);

                i++;
            }
        }

    }


}
